create or replace package ac_diff_not_in is

  -- Author  : LEV ERUSALIMSKIY
  -- Created : 13/01/2012 8:43:53 PM

 procedure app_appl_authorization_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);

 procedure app_page_items_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_page_id number);

 procedure appl_items_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);

 procedure app_appl_lov_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);

 procedure app_report_cols_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, pi_region_id1 number, pi_region_id2 number);

 procedure app_page_branches_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number);

 procedure app_regions_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number);

 procedure app_page_validations_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number);

 procedure app_auth_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);

 procedure app_pages_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort in out number);

 procedure app_comp_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);

 procedure app_proc_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);

 procedure app_page_buttons_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number);

 procedure app_page_dyn_actions_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number);

 procedure app_page_report_ir_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number);

 procedure app_page_process(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number);

 procedure app_page_comp(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number);

 procedure app_report_ir_col_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number);

 procedure app_subst_string(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);
 
 procedure app_nav_bar  (p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);

 procedure app_shortcuts(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);
 
 procedure app_themes   (p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);

 procedure app_templ    (p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);

 procedure app_lov_entr (p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_lov_name varchar2);
 
 procedure app_templ_bc (p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);
 
 procedure app_templ_button(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);
 
 procedure app_templ_calendar(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);
 
 procedure app_templ_label(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);

 procedure app_templ_list(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);
 
 procedure app_templ_page(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);
 
 procedure app_templ_lov (p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);

 procedure app_templ_region(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);

 procedure app_templ_report(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);

 procedure app_list(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);
 
 procedure app_lists_entries(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_list_name varchar2);
 
 procedure app_breadcrumbs  (p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number );
 
 procedure app_bc_entries   (p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_breadcrumb_id1 number, pi_breadcrumb_id2 number); 

 procedure app_page_da_actions(p_result_id number,  pi_parent_id number, pi_app_id1  number, pi_app_id2 number, pi_page_id  number,  pi_da_name varchar2);

 procedure app_page_ir_comp(p_result_id number,  pi_parent_id  number,  pi_app_id1   number,  pi_app_id2   number,  pi_page_id  number);

end ac_diff_not_in;
/

create or replace package body ac_diff_not_in is

 procedure app_page_items_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_page_id number) is
   v_parent_id number;
   v_msg      varchar2(1000);
   begin

   v_msg       := ac_utils.in1NotIn2('Items', pi_app_id1, pi_app_id2);
   v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_msg, 1, true, ac_utils.G_PAGE_ITEMS );

   insert into ac_results
   (result_id,   parent_id,    param_name, is_branch, is_src)
   select p_result_id, pi_parent_id, t.item_name, 'N', 'N' from
   (
    select app1.item_name from apex_application_page_items app1
    where app1.application_id = pi_app_id1
      and app1.page_id = p_page_id
     minus
    select app1.item_name from apex_application_page_items app1
    where app1.application_id = pi_app_id2
      and app1.page_id = p_page_id
    ) t;
 end app_page_items_not_in;

 procedure appl_items_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
 v_parent_id      number;
 v_title          varchar2(1000) := 'Appl Items';
 v_msg            varchar2(1000);
 begin

   v_msg         := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
   v_parent_id   := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_msg, 1, true, ac_utils.G_APPLICATION_ITEM );
   insert into ac_results
   (result_id,     parent_id, param_name, is_branch, is_src)
   select
    p_result_id, v_parent_id, t.item_name, 'N', 'N' from
   (
       select ai.item_name
       from  apex_application_items ai
       where ai.application_id = pi_app_id1
       minus
       select ai.item_name
       from  apex_application_items ai
       where ai.application_id = pi_app_id2
   ) t;

 end  appl_items_not_in;

 procedure app_appl_lov_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
 v_parent_id_1    number;
 v_msg            varchar2(1000);
 v_title          varchar2(1000) := 'LOVS';
 begin
    v_msg         := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
    v_parent_id_1 := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_msg, 1, true, ac_utils.G_LOVS );
    insert into ac_results
    (result_id,   parent_id,     param_name, is_branch, is_src)
    select
     p_result_id, v_parent_id_1, t.list_of_values_name, 'N', 'N'
    from
    (
     select t.list_of_values_name from APEX_APPLICATION_LOVS t
       where t.application_id = pi_app_id1
      minus
      select t.list_of_values_name from APEX_APPLICATION_LOVS t
       where t.application_id = pi_app_id2
    ) t;

 end  app_appl_lov_not_in;

 procedure app_pages_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort in out number) is
   v_parent_id number;
   v_msg       varchar2(1000);
   begin
     v_msg := ac_utils.in1NotIn2('Pages', pi_app_id1, pi_app_id2);

     v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_msg, p_sort, true, ac_utils.G_PAGE);
     insert into ac_results
     (result_id, parent_id, param_name, is_branch, is_src)
     select p_result_id, v_parent_id, t.page_id, 'N', 'N' from
      ( select app.page_id
        from apex_application_pages app
        where app.application_id = pi_app_id1
         minus
        select app.page_id
        from apex_application_pages app
        where app.application_id = pi_app_id2
      ) t;
 end app_pages_not_in;

 procedure app_report_cols_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, pi_region_id1 number, pi_region_id2 number) is
   v_parent_id      number;
   v_parent_id_cols number;
   v_column_alias   varchar2(100) := '**==**';
   v_msg            varchar2(1000);
   v_title          varchar2(100) := 'Report columns';
   begin

     v_msg := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);

     v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_msg, 1, true, ac_utils.G_REPORT_COLUMN);

     insert into ac_results
     (result_id, parent_id, param_name, is_branch, is_src)
     select p_result_id, v_parent_id, t.COLUMN_ALIAS, 'N', 'N' from
      ( select rc1.COLUMN_ALIAS
        from apex_application_page_rpt_cols rc1
        where rc1.application_id = pi_app_id1
          and rc1.page_id = pi_page_id
          and rc1.region_id = pi_region_id1
         minus
        select rc2.COLUMN_ALIAS
        from apex_application_page_rpt_cols rc2
        where rc2.application_id = pi_app_id2
          and rc2.page_id = pi_page_id
          and rc2.region_id = pi_region_id2
       ) t;
 end app_report_cols_not_in;

 procedure app_page_branches_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number) is
  v_parent_id   number;
  v_parent_id_1 number;
  v_branch_sig  varchar2(100) := '******';
  v_msg         varchar2(1000);
  v_title       varchar2(1000) := 'Branches';
 begin
    v_msg := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);

    v_parent_id_1 := ac_utils.ins_result_msg(p_result_id,  pi_parent_id,  v_msg,   1, true, ac_utils.G_BRANCH);

     insert into ac_results
     (result_id, parent_id, param_name, is_branch, is_src)
     select p_result_id, v_parent_id_1, t.BRANCH_POINT||':'||t.process_sequence, 'N', 'N'  from
      ( select t.BRANCH_POINT, t.process_sequence
        from APEX_APPLICATION_PAGE_BRANCHES t
          where t.application_id = pi_app_id1
            and t.page_id = pi_page_id
          minus
        select t.BRANCH_POINT, t.process_sequence
        from APEX_APPLICATION_PAGE_BRANCHES t
        where t.application_id = pi_app_id2
            and t.page_id = pi_page_id
       ) t;

 end  app_page_branches_not_in;

 procedure app_regions_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number) is
 v_parent_id number;
 v_title     varchar2(100) := 'Regions';
 v_msg       varchar2(1000);
 begin
   v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
   v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_msg, 1);
   insert into ac_results
   (result_id,   parent_id,    param_name, is_branch, is_src)
   select p_result_id, v_parent_id, 'Display seq:'|| t.display_sequence, 'N', 'N' from
   (select reg1.display_sequence 
    from apex_application_page_regions reg1
    where reg1.application_id = pi_app_id1
      and reg1.page_id = pi_page_id
     minus
    select reg2.display_sequence 
    from apex_application_page_regions reg2
    where reg2.application_id = pi_app_id2
      and reg2.page_id = pi_page_id
    ) t;

 end  app_regions_not_in;

 procedure app_page_validations_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number) is
 v_title          varchar2(1000) := ac_utils.G_VALIDATIONS;
 v_msg            varchar2(1000);
 v_parent_id      number;
 begin
    v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
    v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_msg,   1, true, ac_utils.G_VALIDATIONS );
    insert into ac_results
    (result_id,   parent_id,     param_name, is_branch, is_src)
    select
     p_result_id, v_parent_id, t.validation_name, 'N', 'N'
    from
    (
     select t.validation_name from APEX_APPLICATION_PAGE_VAL t
       where t.application_id = pi_app_id1
         and t.page_id = pi_page_id
      minus
      select t.validation_name from APEX_APPLICATION_PAGE_VAL t
       where t.application_id = pi_app_id2
         and t.page_id = pi_page_id
    ) t;

 end app_page_validations_not_in;

 procedure app_auth_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
 v_title          varchar2(1000) := ac_utils.G_AUTH_SCHEMES;
 v_msg            varchar2(1000);
 v_parent_id      number;
 begin
    v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
    v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_msg,   1, true, ac_utils.G_AUTH_SCHEMES );
    insert into ac_results
    (result_id,   parent_id,   param_name, is_branch, is_src)
    select
     p_result_id, v_parent_id, t.authentication_scheme_name, 'N', 'N'
    from
    (
     select t.authentication_scheme_name from APEX_APPLICATION_AUTH t
       where t.application_id = pi_app_id1
      minus
      select t.authentication_scheme_name from APEX_APPLICATION_AUTH t
       where t.application_id = pi_app_id2
    ) t;
 end  app_auth_not_in;


 procedure app_comp_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
 v_parent_id number;
 v_title     varchar2(100) := ac_utils.in1NotIn2('Computations', pi_app_id1, pi_app_id2);
 begin
     v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_title, 1, true, ac_utils.G_COMPUTATION );
     insert into ac_results
     (result_id,   parent_id,    param_name, is_branch, is_src)
     select p_result_id, v_parent_id, t.COMPUTATION_ITEM, 'N', 'N' from
     (select c.COMPUTATION_ITEM
      from APEX_APPLICATION_COMPUTATIONS c
      where c.application_id = pi_app_id1
       minus
      select c.COMPUTATION_ITEM
      from APEX_APPLICATION_COMPUTATIONS c
      where c.application_id = pi_app_id2
     ) t;

 end  app_comp_not_in;

 procedure app_proc_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
  v_parent_id number;
  v_msg       varchar2(1000);
  v_title     varchar2(1000) := 'Processes';
 begin
   v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
   v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_msg, 1 , true, ac_utils.G_PROCESS);

   insert into ac_results
   (result_id,   parent_id,    param_name, is_branch, is_src)
   select p_result_id, v_parent_id, t.PROCESS_NAME, 'N', 'N' from
   (select c.PROCESS_NAME
    from APEX_APPLICATION_Processes c
    where c.application_id = pi_app_id1
     minus
    select c.PROCESS_NAME
    from APEX_APPLICATION_Processes c
    where c.application_id = pi_app_id2
   ) t;

 end  app_proc_not_in;

 procedure app_page_buttons_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number) is
 v_parent_id   number;
 v_title       varchar2(10)  := 'Buttons';
 v_msg         varchar2(1000);
 begin
    v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
    v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_msg, 1 , true, ac_utils.G_BUTTON );
    insert into ac_results
     (result_id,   parent_id,    param_name, is_branch, is_src)
     select p_result_id, v_parent_id, t.BUTTON_NAME,'N', 'N' from
     (select b.BUTTON_NAME
      from apex_application_page_buttons b
      where b.application_id = pi_app_id1
        and b.page_id =  pi_page_id
       minus
      select  b.BUTTON_NAME
      from apex_application_page_buttons b
      where b.application_id = pi_app_id2
        and b.page_id =  pi_page_id
      ) t;
 end app_page_buttons_not_in;

 procedure app_page_dyn_actions_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number) is
 v_parent_id   number;
 v_title       varchar2(20)  := ac_utils.G_DYN_ACTIONS;
 v_msg         varchar2(1000);
 begin
    v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
    v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_msg, 1 , true, ac_utils.G_DYN_ACTIONS );
    insert into ac_results
     (result_id,   parent_id,    param_name, is_branch, is_src)
     select p_result_id, v_parent_id, t.dynamic_action_name ,'N', 'N' from
     (select da.dynamic_action_name
      from apex_application_page_da da
      where da.application_id = pi_app_id1
        and da.page_id =  pi_page_id
       minus
      select  da.dynamic_action_name
      from apex_application_page_da da
      where da.application_id = pi_app_id2
        and da.page_id =  pi_page_id
      ) t;
 end app_page_dyn_actions_not_in;

 procedure app_appl_authorization_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
 v_parent_id   number;
 v_title       varchar2(60)  := ac_utils.G_AUTHORIZATION ;
 v_msg         varchar2(1000);
 begin
   v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
   v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_AUTHORIZATION );
    insert into ac_results
    (result_id,            parent_id, param_name,                  is_branch, is_src)
     select p_result_id, v_parent_id, t.authorization_scheme_name ,'N', 'N' from
    (select auth.authorization_scheme_name
      from apex_application_authorization auth
      where auth.application_id = pi_app_id1
       minus
      select  auth.authorization_scheme_name
      from apex_application_authorization auth
      where auth.application_id = pi_app_id2
     ) t;
 end  app_appl_authorization_not_in;

 procedure app_page_report_ir_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number) is
 v_parent_id   number;
 v_title       varchar2(60)  := ac_utils.G_AUTHORIZATION ;
 v_msg         varchar2(1000);
 begin
   v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
   v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_AUTHORIZATION );
    insert into ac_results
    (result_id,            parent_id, param_name,                  is_branch, is_src)
     select p_result_id, v_parent_id, t.region_name ,'N', 'N' from
    (select ir.region_name
       from apex_application_page_ir ir
      where ir.application_id = pi_app_id1
        and ir.page_id = pi_page_id
      minus
     select ir.region_name
       from apex_application_page_ir ir
      where ir.application_id = pi_app_id2
        and ir.page_id = pi_page_id
     ) t;
 end  app_page_report_ir_not_in;

 procedure app_report_ir_col_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number) is
 v_parent_id   number;
 v_title       varchar2(60) := ac_utils.G_INTERACTIVE_REPORT_COLS ;
 v_msg         varchar2(1000);
 begin
   v_msg  := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
   v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_INTERACTIVE_REPORT_COLS );

    insert into ac_results
    (result_id,            parent_id, param_name,                  is_branch, is_src)
     select p_result_id, v_parent_id, t.column_alias ,'N', 'N' from
    (select ir.column_alias
       from apex_application_page_ir_col ir
      where ir.application_id = pi_app_id1
        and ir.page_id = pi_page_id
      minus
     select ir.column_alias
       from apex_application_page_ir_col ir
      where ir.application_id = pi_app_id2
        and ir.page_id = pi_page_id
     ) t;

 end  app_report_ir_col_not_in;

 procedure app_page_process(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number) is
 v_parent_id   number;
 v_title       varchar2(60) := ac_utils.G_PAGE_PROCESS;
 v_msg         varchar2(1000);
  begin
   v_msg  := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
   v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_PAGE_PROCESS );
   insert into ac_results
    (result_id,            parent_id,
     param_name,           is_branch, is_src)
     select p_result_id, v_parent_id,
     t.process_point || ' seq:'|| t.execution_sequence ,'N', 'N' from
    (select pr.process_point, pr.execution_sequence
      from apex_application_page_proc pr
      where pr.application_id = pi_app_id1
        and pr.page_id = pi_page_id
      minus
      select pr.process_point, pr.execution_sequence
      from apex_application_page_proc pr
      where pr.application_id = pi_app_id2
        and pr.page_id = pi_page_id
    ) t;
 end app_page_process;

 procedure app_page_comp(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number) is
 v_parent_id   number;
 v_title       varchar2(60) := ac_utils.G_COMPUTATION;
 v_msg         varchar2(1000);
 begin
   v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
   v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_COMPUTATION );

   insert into ac_results
    (result_id,            parent_id,
     param_name,           is_branch, is_src)
     select p_result_id, v_parent_id,
     t.COMPUTATION_POINT || ' seq:'|| t.execution_sequence ,'N', 'N' from
    (select c1.COMPUTATION_POINT, c1.execution_sequence
      from apex_application_page_comp c1
      where c1.application_id = pi_app_id1
        and c1.page_id = pi_page_id
      minus
      select c2.COMPUTATION_POINT, c2.execution_sequence
      from apex_application_page_comp c2
      where c2.application_id = pi_app_id2
        and c2.page_id = pi_page_id
    ) t;
     
 end  app_page_comp;

 procedure app_subst_string(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
 v_parent_id   number;
 v_title       varchar2(60) := ac_utils.G_SUBST_STRINGS;
 v_msg         varchar2(1000);
 begin
  v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
  v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_SUBST_STRINGS );

  insert into ac_results
 (result_id,          parent_id,   param_name,           is_branch, is_src)
  select p_result_id, v_parent_id, t.substitution_string , 'N', 'N' 
  from
  (
      select app_s1.substitution_string
      from  APEX_APPLICATION_SUBSTITUTIONS  app_s1
       where app_s1.application_id = pi_app_id1
      minus
      select app_s2.substitution_string 
      from  APEX_APPLICATION_SUBSTITUTIONS  app_s2
       where app_s2.application_id = pi_app_id2
  ) t;     
 end app_subst_string;

 procedure app_nav_bar(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
 v_parent_id   number;
 v_title       varchar2(60) := ac_utils.G_NAV_BAR;
 v_msg         varchar2(1000);
 begin
  v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
  v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_NAV_BAR );
  
  insert into ac_results
 (result_id,          parent_id,   param_name,           is_branch, is_src)
  select p_result_id, v_parent_id, 'displ. sequence:' || t.display_sequence , 'N', 'N' 
  from
  (   select app_nb1.display_sequence
      from  APEX_APPLICATION_NAV_BAR   app_nb1
       where app_nb1.application_id = pi_app_id1
      minus
      select app_nb2.display_sequence 
      from  APEX_APPLICATION_NAV_BAR  app_nb2
       where app_nb2.application_id = pi_app_id2
  ) t;     
 end app_nav_bar;

 procedure app_shortcuts(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
 v_parent_id   number;
 v_title       varchar2(60) := ac_utils.G_SHORTCUTS ;
 v_msg         varchar2(1000);
 begin
     v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
     v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_SHORTCUTS );
   
    insert into ac_results
    (result_id,         parent_id,   param_name,     is_branch, is_src)
    select p_result_id, v_parent_id, t.SHORTCUT_NAME , 'N', 'N' 
    from
    ( 
     select s1.SHORTCUT_NAME 
     from apex_application_shortcuts s1
     where s1.application_id = pi_app_id1
     minus
     select s1.SHORTCUT_NAME 
     from apex_application_shortcuts s1
     where s1.application_id = pi_app_id2
    ) t;
 end  app_shortcuts;

 procedure app_themes(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
 v_parent_id   number;
 v_title       varchar2(60) := ac_utils.G_APP_THEMES;
 v_msg         varchar2(1000);
 begin
     v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
     v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_APP_THEMES );
     insert into ac_results
     (result_id,         parent_id,   param_name,     is_branch, is_src)
      select p_result_id, v_parent_id, t.theme_name , 'N', 'N' 
      from
      (
        select t.theme_name from APEX_APPLICATION_THEMES t
        where t.application_id = pi_app_id1
        minus
        select t.theme_name from APEX_APPLICATION_THEMES t
        where t.application_id = pi_app_id2
      ) t;
     
 end app_themes;

 procedure app_templ(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
 v_parent_id   number;
 v_title       varchar2(60) := ac_utils.G_APP_TEMPLATES;
 v_msg         varchar2(1000);
 begin
     v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
     v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_APP_TEMPLATES );

     insert into ac_results
     (result_id,            parent_id, param_name,     is_branch, is_src)
     select p_result_id, v_parent_id, t.tn ,          'N',       'N' 
     from
     ( select tmpl.theme_number||':'||tmpl.template_name tn
        from apex_application_templates tmpl
         where tmpl.application_id = pi_app_id1
        minus
       select tmpl.theme_number||':'||tmpl.template_name tn
        from apex_application_templates tmpl
         where tmpl.application_id = pi_app_id2
     ) t;
 end;

 procedure app_lov_entr(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_lov_name varchar2) is
 v_parent_id   number;
 v_msg         varchar2(1000);
 begin
     v_msg       := ac_utils.in1NotIn2('LOV Entries', pi_app_id1, pi_app_id2);
     v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg , 1, true, AC_UTILS.G_LOV_ENTRIES );

     insert into ac_results
     (result_id,           parent_id, param_name,                          is_branch, is_src)
     select p_result_id, v_parent_id, 'Displ. seq:'||t.display_sequence ,  'N',       'N' 
     from
     (   select l_entr1.display_sequence  
         from apex_application_lov_entries l_entr1 
         where l_entr1.application_id      = pi_app_id1 
           and l_entr1.list_of_values_name = pi_lov_name
          minus
         select l_entr2.display_sequence  
         from apex_application_lov_entries l_entr2
         where l_entr2.application_id = pi_app_id2
           and l_entr2.list_of_values_name = pi_lov_name
     ) t;

 end app_lov_entr;

 procedure app_TEMPL_BC(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
 v_parent_id   number;
 v_title       varchar2(60) := 'Breadcrumb Template';
 v_msg         varchar2(1000);
 begin
     v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
     v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_TEMPL_BREADCRUMB );
     
     insert into ac_results
     (result_id,           parent_id, param_name,                          is_branch, is_src)
     select p_result_id, v_parent_id, theme_number||':'|| template_name  , 'N',       'N' 
     from
     (     
       select l_temp1.theme_number, l_temp1.template_name
       from apex_application_temp_bc  l_temp1 
        where l_temp1.application_id  =  pi_app_id1 
         minus
        select l_temp1.theme_number, l_temp1.template_name
        from apex_application_temp_bc  l_temp1 
        where l_temp1.application_id  =  pi_app_id2
      );
           
 end  app_TEMPL_BC;

 procedure app_TEMPL_BUTTON(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
 v_parent_id   number;
 v_title       varchar2(60) := 'Button Template';
 v_msg         varchar2(1000);
 begin
     v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
     v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_TEMPL_BUTTON );
     
     insert into ac_results
     (result_id,           parent_id, param_name,                          is_branch, is_src)
     select p_result_id, v_parent_id, theme_number||':'|| template_name  , 'N',       'N' 
     from
     (     
       select l_temp_butt1.theme_number, l_temp_butt1.template_name
       from APEX_APPLICATION_TEMP_BUTTON  l_temp_butt1 
        where l_temp_butt1.application_id  =  pi_app_id1 
         minus
        select l_temp_butt1.theme_number, l_temp_butt1.template_name
        from APEX_APPLICATION_TEMP_BUTTON  l_temp_butt1 
        where l_temp_butt1.application_id  =  pi_app_id2
     );
           
 end  app_TEMPL_BUTTON;

 procedure app_TEMPL_CALENDAR(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
 v_parent_id   number;
 v_title       varchar2(60) := 'Calendar Template';
 v_msg         varchar2(1000);
 begin
     v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
     v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_TEMPL_CALENDAR);
     
     insert into ac_results
     (result_id,           parent_id, param_name,                          is_branch, is_src)
     select p_result_id, v_parent_id, theme_number||':'|| template_name  , 'N',       'N' 
     from
     (     
       select l_temp_butt1.theme_number, l_temp_butt1.template_name
       from APEX_APPLICATION_TEMP_CALENDAR  l_temp_butt1 
        where l_temp_butt1.application_id  =  pi_app_id1 
         minus
        select l_temp_butt1.theme_number, l_temp_butt1.template_name
        from APEX_APPLICATION_TEMP_CALENDAR  l_temp_butt1 
        where l_temp_butt1.application_id  =  pi_app_id2
     );
           
 end  app_TEMPL_CALENDAR;

 procedure app_TEMPL_LABEL(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
 v_parent_id   number;
 v_title       varchar2(60) := 'Label Template';
 v_msg         varchar2(1000);
 begin
     v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
     v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_TEMPL_LABEL);
     
     insert into ac_results
     (result_id,           parent_id, param_name,                          is_branch, is_src)
     select p_result_id, v_parent_id, theme_number||':'|| template_name  , 'N',       'N' 
     from
     (     
       select l_temp_l2.theme_number, l_temp_l2.template_name
       from APEX_APPLICATION_TEMP_LABEL  l_temp_l2 
        where l_temp_l2.application_id  =  pi_app_id1 
         minus
        select l_temp_l1.theme_number, l_temp_l1.template_name
        from APEX_APPLICATION_TEMP_LABEL  l_temp_l1 
        where l_temp_l1.application_id  =  pi_app_id2
     );
           
 end  app_TEMPL_LABEL;

 procedure app_TEMPL_LIST(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
 v_parent_id   number;
 v_title       varchar2(60) := 'List Template';
 v_msg         varchar2(1000);
 begin
     v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
     v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_TEMPL_LIST);
     
     insert into ac_results
     (result_id,           parent_id, param_name,                          is_branch, is_src)
     select p_result_id, v_parent_id, theme_number||':'|| template_name  , 'N',       'N' 
     from
     (     
       select l2.theme_number, l2.template_name
       from APEX_APPLICATION_TEMP_LIST l2 
        where l2.application_id  =  pi_app_id1 
         minus
        select l1.theme_number, l1.template_name
        from APEX_APPLICATION_TEMP_LIST l1 
        where l1.application_id  =  pi_app_id2
     );
           
 end  app_TEMPL_LIST;

 procedure app_TEMPL_PAGE(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
 v_parent_id   number;
 v_title       varchar2(60) := 'Page Template';
 v_msg         varchar2(1000);
 begin
     v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
     v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_TEMPL_LIST);
     
     insert into ac_results
     (result_id,           parent_id, param_name,                          is_branch, is_src)
     select p_result_id, v_parent_id, theme_number||':'|| template_name  , 'N',       'N' 
     from
     (     
       select l2.theme_number, l2.template_name
       from APEX_APPLICATION_TEMP_PAGE l2 
        where l2.application_id  =  pi_app_id1 
         minus
        select l1.theme_number, l1.template_name
        from APEX_APPLICATION_TEMP_PAGE l1 
        where l1.application_id  =  pi_app_id2
     );
           
 end  app_TEMPL_PAGE;
 
 procedure app_TEMPL_LOV(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
 v_parent_id   number;
 v_title       varchar2(60) := 'LOV Template';
 v_msg         varchar2(1000);
 begin
     v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
     v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_TEMPL_LOV);
     
     insert into ac_results
     (result_id,           parent_id, param_name,    is_branch, is_src)
     select p_result_id, v_parent_id, theme_number||':'|| theme_class  , 'N',       'N' 
     from
     (     
       select l2.theme_number, l2.theme_class
       from APEX_APPLICATION_TEMP_POPUPLOV l2 
        where l2.application_id  =  pi_app_id1 
         minus
        select l1.theme_number, l1.theme_class
        from APEX_APPLICATION_TEMP_POPUPLOV l1 
        where l1.application_id  =  pi_app_id2
     );
           
 end  app_TEMPL_LOV;
 
 procedure app_TEMPL_REGION(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
 v_parent_id   number;
 v_title       varchar2(60) := 'REGION Template';
 v_msg         varchar2(1000);
 begin
     v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
     v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_TEMPL_REGION);
     
     insert into ac_results
     (result_id,           parent_id, param_name,    is_branch, is_src)
     select p_result_id, v_parent_id, theme_number||':'|| template_name  , 'N',       'N' 
     from
     (     
       select r2.theme_number, r2.template_name
       from APEX_APPLICATION_TEMP_REGION r2 
        where r2.application_id  =  pi_app_id1 
         minus
        select r1.theme_number, r1.template_name
        from APEX_APPLICATION_TEMP_REGION r1 
        where r1.application_id  =  pi_app_id2
     );
           
 end  app_TEMPL_REGION;
 
 procedure app_TEMPL_REPORT(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
 v_parent_id   number;
 v_title       varchar2(60) := 'REPORT Template';
 v_msg         varchar2(1000);
 begin
     v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
     v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_TEMPL_REPORT);
     
     insert into ac_results
     (result_id,           parent_id, param_name,    is_branch, is_src)
     select p_result_id, v_parent_id, theme_number||':'|| template_name  , 'N',       'N' 
     from
     (     
       select r2.theme_number, r2.template_name
       from APEX_APPLICATION_TEMP_REPORT r2 
        where r2.application_id  =  pi_app_id1 
         minus
        select r1.theme_number, r1.template_name
        from APEX_APPLICATION_TEMP_REPORT r1 
        where r1.application_id  =  pi_app_id2
     );
           
 end  app_TEMPL_REPORT;

 procedure APP_LIST(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
 v_parent_id   number;
 v_title       varchar2(60) := 'Lists';
 v_msg         varchar2(1000);
 begin
     v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
     v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_APP_LISTS );
     
     insert into ac_results
     (result_id,           parent_id, param_name,    is_branch, is_src)
     select p_result_id, v_parent_id, list_name,     'N',       'N' 
     from
     (     
       select l2.list_name
       from apex_application_lists l2 
        where l2.application_id  =  pi_app_id1 
         minus
        select l1.list_name
        from apex_application_lists l1 
        where l1.application_id  =  pi_app_id2
     );
 end;

 procedure APP_LISTS_ENTRIES(p_result_id number, pi_parent_id number, 
                             pi_app_id1 number,  pi_app_id2 number,
                             p_list_name varchar2 ) is
 v_parent_id   number;
 v_title       varchar2(60) := 'List Entries';
 v_msg         varchar2(1000);
 begin
     v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
     v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_APP_LISTS_ENTRIES );
     
     insert into ac_results
     (result_id,           parent_id, param_name,    is_branch, is_src)
     select p_result_id, v_parent_id, entry_text,     'N',       'N' 
     from
     (     
       select l2.entry_text
       from APEX_APPLICATION_LIST_ENTRIES l2 
        where l2.application_id  =  pi_app_id1 
          and l2.list_name = p_list_name
         minus
        select l1.entry_text
        from APEX_APPLICATION_LIST_ENTRIES l1 
        where l1.application_id  =  pi_app_id2
          and l1.list_name = p_list_name
     );
 end APP_LISTS_ENTRIES;
 
 procedure APP_BREADCRUMBS(p_result_id number, pi_parent_id number, pi_app_id1 number,  pi_app_id2 number ) is
 v_parent_id   number;
 v_title       varchar2(60) := 'Breadcrumbs';
 v_msg         varchar2(1000);
 begin
     v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
     v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_APP_BREADCRUMBS );
     
     insert into ac_results
     (result_id,           parent_id, param_name,       is_branch, is_src)
     select p_result_id, v_parent_id, breadcrumb_name,  'N',       'N' 
     from
     (     
       select l2.breadcrumb_name
       from apex_application_breadcrumbs l2 
        where l2.application_id  =  pi_app_id1 
         minus
        select l1.breadcrumb_name
        from apex_application_breadcrumbs l1 
        where l1.application_id  =  pi_app_id2
     );
 end  APP_BREADCRUMBS;
 
 procedure APP_BC_ENTRIES(p_result_id number,       pi_parent_id number, 
                          pi_app_id1 number,        pi_app_id2 number, 
                          pi_breadcrumb_id1 number, pi_breadcrumb_id2 number 
                         ) is
 v_parent_id   number;
 v_title       varchar2(60) := 'Breadcrumb Entries';
 v_msg         varchar2(1000);
 begin
     v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
     v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_APP_BC_ENTRIES );
     
     insert into ac_results
     (result_id,           parent_id, param_name,  is_branch, is_src)
     select p_result_id, v_parent_id, entry_label, 'N',       'N' 
     from
     (     
       select bce1.entry_label
       from apex_application_bc_entries bce1 
        where bce1.application_id  =  pi_app_id1 
          and bce1.breadcrumb_id   =  pi_breadcrumb_id1
         minus
        select bce2.entry_label
        from apex_application_bc_entries bce2
        where bce2.application_id  =  pi_app_id2
          and bce2.breadcrumb_id   =  pi_breadcrumb_id2
     );
 end  APP_BC_ENTRIES;
 
 procedure app_page_da_actions(p_result_id number,  pi_parent_id number, pi_app_id1  number,  pi_app_id2   number, 
                               pi_page_id  number,  pi_da_name   varchar2 ) is
 v_parent_id   number;
 v_title       varchar2(60) := 'Actions of DA';
 v_msg         varchar2(1000);
 begin
  v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
  v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_DA_ACTIONS );
     
  insert into ac_results
  (result_id,           parent_id, param_name,  is_branch, is_src)
  select p_result_id, v_parent_id, 'Seq:'||action_sequence, 'N',       'N' 
  from
     (     
       select daa.action_sequence
       from apex_application_page_da_acts daa 
       where daa.application_id  =  pi_app_id1 
         and daa.page_id         =  pi_page_id
         and daa.dynamic_action_name =  pi_da_name
         minus
       select daa.action_sequence
       from apex_application_page_da_acts daa 
       where daa.application_id  =  pi_app_id2
         and daa.page_id         =  pi_page_id
         and daa.dynamic_action_name =  pi_da_name
     );
 end  app_page_da_actions;                               
 
 procedure app_page_ir_comp(p_result_id number,  pi_parent_id  number,  pi_app_id1   number,  pi_app_id2   number,  pi_page_id  number) is
 v_parent_id   number;
 v_title       varchar2(60) := 'Comput. in user-level report settings for an IR';
 v_msg         varchar2(1000);
 begin
  v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
  v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_DA_ACTIONS );
     
  insert into ac_results
  (result_id,           parent_id, param_name,  is_branch, is_src)
  select p_result_id, v_parent_id, COMPUTATION_COLUMN_ALIAS, 'N',       'N' 
  from
     (     
       select daa.COMPUTATION_COLUMN_ALIAS
       from APEX_APPLICATION_PAGE_IR_COMP daa 
       where daa.application_id  =  pi_app_id1 
         and daa.page_id         =  pi_page_id
         minus
       select daa.COMPUTATION_COLUMN_ALIAS
       from APEX_APPLICATION_PAGE_IR_COMP daa 
       where daa.application_id  =  pi_app_id2
         and daa.page_id         =  pi_page_id
     );
     
 end app_page_ir_comp;
 
end ac_diff_not_in;
/

